/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package my.cbr.database;

import com.mysql.management.driverlaunched.ServerLauncherSocketFactory;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import jcolibri.cbrcore.CBRCase;
import my.cbr.casedescriptions.CaseDescription;
import my.cbr.casedescriptions.CaseSolution;
import my.cbr.database.model.AquacultureProdUnit;
import my.cbr.database.model.AquacultureSite;
import my.cbr.database.model.FishDeath;
import my.cbr.database.model.HatcheryCompanyID;
import my.cbr.database.model.SpeciesOrigin;
import my.cbr.database.model.Vaccine;
import my.cbr.database.model.measurement.MeasurementType;
import my.cbr.database.model.measurement.OxygenInstance;
import my.cbr.database.model.measurement.TemperatureInstance;
import my.cbr.database.model.ApproximationNumber;
import my.cbr.utils.LinearApproximation;
import my.cbr.utils.Utility;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

/**
 *
 * @author Geir Ole
 */
public class CaseGenerator {
    private List<AquacultureSite> sites = new ArrayList<AquacultureSite>();
    private List<SpeciesOrigin> origins = new ArrayList<SpeciesOrigin>();
    private List<HatcheryCompanyID> hatchery = new ArrayList<HatcheryCompanyID>();
    private List<Vaccine> vaccine = new ArrayList<Vaccine>();
    private LinearApproximation temperatureLinearApproximation = new LinearApproximation();
    private LinearApproximation oxygenLinearApproximation = new LinearApproximation();
    public static String serverName = "localhost";
    public static Integer port = 1337;
    public static String mydatabase = "simframepilotver02";
    public static String myCaseBase = "FishFarmingCaseBase";
    public static String username = "root";
    public static String password = "";
    private static Integer DAYS = 35;
    private File basedir = null;
    private List<Double> death = new ArrayList<Double>();
    
    /**
     * @deprecated use Mysqldbserver.getconn()
     * @param database
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException 
     */
    private Connection getConnection(String database) throws ClassNotFoundException, SQLException
    {
        basedir = new File(System.getProperty("user.dir"));
        File databasedir = new File(basedir, "mxj-tmp");
        Connection connection = null;
        try {
            // Load the JDBC driver
            String driverName = "com.mysql.jdbc.Driver"; // MySQL MM JDBC driver
            Class.forName(driverName);

            // Create a connection to the database
            String url = "jdbc:mysql:mxj://" + serverName + ":" + port +  "/" + database
                    + "?createDatabaseIfNotExist=true"
                    + "&server.basedir=" + databasedir; // a JDBC url
            connection = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            throw new ClassNotFoundException("Class not found", e);
            // Could not find the database driver
        } catch (SQLException e) {
            throw new SQLException("SQLException", e);
            // Could not connect to the database
        }
        return connection;
    }
    
    /**
     * @deprecated use mysqldbserver.shutdown()
     * @param connection 
     */
    public void close(Connection connection)
    {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception exception) {
        }
        ServerLauncherSocketFactory.shutdown(basedir, null);
    }
    
    private void dropAndCreateDatabase(Connection connection, String database) throws SQLException, ClassNotFoundException
    {
        Statement statement = connection.createStatement();
        statement.execute("DROP DATABASE if exists "+database);
        statement.execute("CREATE DATABASE "+database);
        statement.close();
    }

    // Creates the query case from the input of the user
    public CBRCase generateQueryCase(List<Integer> sendingProd, List<Integer> starvationDays, int recievingProd, String operationDate, int site) throws ClassNotFoundException, SQLException, ParseException {

        CBRCase queryCase = new CBRCase();

        MySQLDBServer.database = CaseGenerator.mydatabase;
        MySQLDBServer.setConnection();
        Connection con = MySQLDBServer.getConnection();
        Statement stat = con.createStatement();

        CaseDescription query = new CaseDescription();
        queryCase.setDescription(query);
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        query.setDateTime(simpleDateFormat.parse(operationDate));

        AquacultureSite aqSite = new AquacultureSite();
        aqSite.setSiteID(site);

        stat.execute(String.format(SQL_GET_SITE_INFO, site));
        ResultSet siteInfo = stat.getResultSet();
        while(siteInfo.next())
        {
            Integer capacity = siteInfo.getInt("Capacity");
            aqSite.setCapacity(capacity);
        }
        stat.close();

        MeasurementType<TemperatureInstance> temperature = new MeasurementType<TemperatureInstance>(new TemperatureInstance());
        MeasurementType<OxygenInstance> oxygen = new MeasurementType<OxygenInstance>(new OxygenInstance());

        this.setMeasurements(con, temperature, oxygen, site, operationDate.toString());

        query.setAquacultureSite(aqSite);
        query.setOxygen(oxygen);
        query.setTemperature(temperature);

        stat = con.createStatement();
        List<AquacultureProdUnit> sendingUnits = new ArrayList<AquacultureProdUnit>();
        AquacultureProdUnit sendingUnit;
        AquacultureProdUnit recieving = new AquacultureProdUnit(recievingProd);

        //get the sending fish groups
        for (int i = 0; i < sendingProd.size(); i++)
        {
            sendingUnit = new AquacultureProdUnit(sendingProd.get(i));
            sendingUnit.setDescriptionID(query);
            
            String sql = String.format(SQL_SENDING_FG2, operationDate, sendingProd.get(i));
            stat.execute(sql);
            ResultSet rs = stat.getResultSet();
            while(rs.next())
            {
                int fishGroup = rs.getInt("idFishGroup");
                int prodUnitID = rs.getInt("AquacultureProdUnit_idAquacultureProdUnit");
                sendingUnit.setAquacultureProdUnitID(prodUnitID);
                //find fishgroup information like hatcheries, vaccines and origins
                this.findFishInfo(con, fishGroup, sendingUnit);
            }

            this.findIndividCount(con, sendingUnit, operationDate);
            sendingUnit.setStarvationDays(starvationDays.get(i));
            
            sendingUnits.add(sendingUnit);
            mapFishInfoToNewUnit(sendingUnit, recieving);
        }
        query.setReceivingProdUnit(recieving);
        query.setSendingProdUnits(sendingUnits);

        return queryCase;
    }


    private void mapFishInfoToNewUnit(AquacultureProdUnit sending, AquacultureProdUnit recieving)
    {
        for (int i = 0; i < sending.getVaccine().size(); i++) {
            recieving.getVaccine().add(sending.getVaccine().get(i));
        }
        
        for (int i = 0; i < sending.getHatcheryCompanyID().size(); i++) {
            recieving.getHatcheryCompanyID().add(sending.getHatcheryCompanyID().get(i));
        }
       
        for (int i = 0; i < sending.getSpeciesOrigin().size(); i++) {
            recieving.getSpeciesOrigin().add(sending.getSpeciesOrigin().get(i));
            
        }

    }

    public CaseSolution generateSolutions(Integer idFishGroup, Date operationDate, Integer interval, Connection connection) throws SQLException
    {
        // Create a caseSolution
       CaseSolution caseSolution = new CaseSolution();

       // Query the database for occurences of fish death for a fishgroup in a given time space
       Statement statement = connection.createStatement();
       statement.execute(String.format(CaseGenerator.SQL_CAUSEOFDEATH_FOR_FISHGROUP_GIVEN_DATE, idFishGroup, operationDate, operationDate, interval));
       ResultSet causeCount = statement.getResultSet();
       
       // Create a FishDeath object for each entry in the resultset
       while(causeCount.next())
       {
           // Find the values
           String cause = causeCount.getString("Cause");
           Integer count = causeCount.getInt("Count");
           Date date = causeCount.getDate("DateTime");
           
           // Create object and set the values
           FishDeath fishDeath = new FishDeath();
           fishDeath.setCause(cause);
           fishDeath.setCount(count);
           fishDeath.setDeathDate(date);
           
           // Create relationship between casesolution and fishdeath
           fishDeath.setSolutionID(caseSolution);
           caseSolution.addFishDeath(fishDeath);
       }
       caseSolution.setRisk(this.getRiskAssesment(caseSolution.getFishdeath()));
       return caseSolution;
    }
    
    public String getRiskAssesment(List<FishDeath> list) {
        Map<String,Integer> map = Utility.getAggregatedCount(list);
        
        Integer deathCount = 0;
        Iterator<Entry<String, Integer>> iterator = map.entrySet().iterator();
        while (iterator.hasNext()) {
            Entry<String, Integer> entry = iterator.next();
            deathCount += entry.getValue();
        }

        String risk;
        if(deathCount <= 300)
            risk = "Low";
        else if(deathCount > 300 && deathCount < 1500)
            risk = "Medium";
        else
            risk = "High";
                    
        //String out = " Risk: Total deathcount="+deathCount+", Aggregated count per cause="+map;
        return risk;//+out;
    }
    
    public void getRisk(CaseSolution caseSolution) {
        List<FishDeath> list = caseSolution.getFishdeath();
        Map<String,Integer> map = Utility.getAggregatedCount(list);
        
        Integer deathCount = 0;
        Iterator<Entry<String, Integer>> iterator = map.entrySet().iterator();
        while (iterator.hasNext()) {
            Entry<String, Integer> entry = iterator.next();
            deathCount += entry.getValue();
        }
        caseSolution.setAggregatedCount(deathCount);
        
        Integer receivingAmount = caseSolution.getCaseDescription().getReceivingProdUnit().getReceivingQuantity();
        Double ratio;
        if(receivingAmount==0)
            ratio = 0.0;
        else
            ratio = (double)deathCount / receivingAmount;//individCount;
        caseSolution.setDeathRatio(ratio);
        double min = 0.000267755;
        double range = 0.1455;
        double part = range / 5;
        // -0.04 to spread out the risks. The distribution is originally 2 high, 2 medium, rest low
        if(ratio==0.0)
            caseSolution.setRisk("No death");
        else if(ratio< min+part)
            caseSolution.setRisk("Low");
        else if(ratio >= min+part && ratio < min+(part*2))
            caseSolution.setRisk("Low/Medium");
        else if(ratio >= min+(part*2) && ratio < min+(part*3))
            caseSolution.setRisk("Medium");
        else if(ratio >= min+(part*3) && ratio < min+(part*4))
            caseSolution.setRisk("Medium/High");
        else
            caseSolution.setRisk("High");
    }
    
    public void setMeasurements(Connection connection, MeasurementType<TemperatureInstance> temperature, MeasurementType<OxygenInstance> oxygen, 
            Integer siteID, String dateTime) throws SQLException {
                // Query the database for the measurements connected to this site
                // Temperature and Oxygen
                Statement statement = connection.createStatement();
                String sql_measurement =
                        String.format(CaseGenerator.SQL_MEASUREMENTNUMERICAL, siteID,
                        CaseGenerator.RESULTTYPENAME_TEMPERATURE, CaseGenerator.RESULTTYPENAME_OXYGEN, dateTime, dateTime);
                
                statement.execute(sql_measurement);
                ResultSet rs = statement.getResultSet();
                
                // Create two Lists to store instances in the given timeline
                List<TemperatureInstance> temps = new ArrayList<TemperatureInstance>();
                List<OxygenInstance> oxygens = new ArrayList<OxygenInstance>();

                // Create a temperature and oxygen objects to keep track of current temperature, 
                // the above instances, trends++
                temperature.setPastInstances(temps);
                oxygen.setPastInstances(oxygens);
                
                // iterate through the resultset
                while(rs.next())
                {
                    // get the fields in the resultset
                    String resultTypeName = rs.getString("ResultTypeName");
                    Double value = rs.getDouble("Value");
                    Date date = rs.getDate("dateTime");
                    
                    // create measurement instances according to thei resultTypeName
                    if(resultTypeName.equals(CaseGenerator.RESULTTYPENAME_TEMPERATURE)) {
                        TemperatureInstance temperatureInstance = new TemperatureInstance(date, value, temperature);
                        temps.add(temperatureInstance);
                    }
                    else if(resultTypeName.equals(CaseGenerator.RESULTTYPENAME_OXYGEN)) {
                        OxygenInstance oxygenInstance = new OxygenInstance(date, value, oxygen);
                        oxygens.add(oxygenInstance);
                    }
                }
                
                // Update the current temperature and oxygen with the last measured one.
                // Compute also the slope of the graph
                if(!temps.isEmpty()) {
                    TemperatureInstance last = temps.get(temps.size()-1);
                    temperature.setCurrentInstance(new TemperatureInstance(last.getDate(), last.getValue(), null));
                    ApproximationNumber tempApproxNumber = 
                            temperatureLinearApproximation.applyLinearApproximation(my.cbr.utils.Utility.listOfMeasurementInstanceToDoubleArray(temps));
                    temperature.setApproximation(tempApproxNumber);
                }
                if(!oxygens.isEmpty()) {
                    OxygenInstance last = oxygens.get(oxygens.size()-1);
                    oxygen.setCurrentInstance(new OxygenInstance(last.getDate(), last.getValue(), null));
                    ApproximationNumber oxygenApproxNumber = 
                            oxygenLinearApproximation.applyLinearApproximation(my.cbr.utils.Utility.listOfMeasurementInstanceToDoubleArray(oxygens));
                    oxygen.setApproximation(oxygenApproxNumber);
                }

                // close and create a new statement
                statement.close();
    }

    public void findFishInfo(Connection connection, int fishGroup, AquacultureProdUnit aquacultureProdUnit) throws SQLException
    {
        //finding hacthery and speciesorigin of the fishgroup
        int index;
        Statement s = connection.createStatement();
        s.execute(String.format(CaseGenerator.SQL_STARTFISHGROUP_INFORMATION, fishGroup));
        ResultSet fishInformation = s.getResultSet();
        while(fishInformation.next())
        {
            HatcheryCompanyID hatcheryCompanyID = new HatcheryCompanyID();
            SpeciesOrigin speciesOrigin = new SpeciesOrigin();

            Integer currentHatchery = fishInformation.getInt("HatcheryCompanyID");
            String currentSpecies = fishInformation.getString("SpeciesOrigin");
            hatcheryCompanyID.setHatcheryID(currentHatchery);
            speciesOrigin.setSpeciesName(currentSpecies);

            // Check if the hatchery and origin already have been used
            // Implemented because of a error with using objects who are equal(.equals-method) in org.hibernate.Session
            // Session doesnt distinguish between equal object saved in session.
            index = 0;
            if((index = this.hatchery.indexOf(hatcheryCompanyID))==-1)
                this.hatchery.add(hatcheryCompanyID);
            else
                hatcheryCompanyID = this.hatchery.get(index);

            index = 0;
            if((index = this.origins.indexOf(speciesOrigin))==-1)
                this.origins.add(speciesOrigin);
            else
                speciesOrigin = this.origins.get(index);

            //add hatchery and origin to produnit
            if(!aquacultureProdUnit.getHatcheryCompanyID().contains(hatcheryCompanyID))
                aquacultureProdUnit.addHatcheryCompany(hatcheryCompanyID);
            if(!aquacultureProdUnit.getSpeciesOrigin().contains(speciesOrigin))
                aquacultureProdUnit.addSpeciesOrigin(speciesOrigin);
        }
        s.close();

        //find the vaccine of the fishgroup
        s = connection.createStatement();
        s.execute(String.format(CaseGenerator.SQL_VACCINE, fishGroup));
        ResultSet localResult = s.getResultSet();
        while(localResult.next()) {
            Vaccine currentVaccine = new Vaccine(localResult.getInt("idVaccine"), localResult.getString("Description"));

            // Check if the vaccine has been used
            // Implemented because of a error with using objects who are equal(.equals-method) in org.hibernate.Session
            // Session doesnt distinguish between equal object saved in session.
            index = 0;
            if((index = this.vaccine.indexOf(currentVaccine))==-1)
                this.vaccine.add(currentVaccine);
            else
                currentVaccine = this.vaccine.get(index);

            //add vaccine to prod unit
            aquacultureProdUnit.addVaccine(currentVaccine);
        }
        s.close();
        
        s = connection.createStatement();
        s.execute(String.format("SELECT SendReceiveQuantity FROM FISHGROUPRELATION WHERE idReceivingFishGroup=%d", fishGroup));
        ResultSet resultSet = s.getResultSet();
        Integer quantity = 0;
        while (resultSet.next()) {            
            Integer receivingAmount = resultSet.getInt(1);
            if(receivingAmount!=null)
                    quantity+=receivingAmount;
        }
        aquacultureProdUnit.setReceivingQuantity(quantity);
        s.close();      
    }

    public void findIndividCount(Connection connection, AquacultureProdUnit aquacultureProdUnit, String date) throws SQLException
    {
        Statement s = connection.createStatement();
        String sql = String.format(CaseGenerator.SQL_INDIVIDCOUNT, aquacultureProdUnit.getAquacultureProdUnitID(), CaseGenerator.RESULTTYPENAME_COUNT, date);
        s.execute(sql);
        ResultSet counts = s.getResultSet();
        while(counts.next())
        {
            aquacultureProdUnit.setIndividCount(counts.getDouble("Value"));
        }
        s.close();
        
    }

    public void findStarvationDays(Connection connection, AquacultureProdUnit aquacultureProdUnit, String date) throws SQLException
    {
        Statement s = connection.createStatement();
        String sql = String.format(CaseGenerator.SQL_FEEDING, date, -CaseGenerator.DAYS, date, aquacultureProdUnit.getAquacultureProdUnitID());
        s.execute(sql);
        ResultSet feedingDates = s.getResultSet();
        Integer starvDays = 0;
        int count = 0;

        //counts each of the last 35 days there have been feedings
        while(feedingDates.next())
        {
            count++;
        }
        //starving days = 35 - number of feeding entries the last 35 days
        starvDays = DAYS - count;

        aquacultureProdUnit.setStarvationDays(starvDays);
    }

    /** 
     * Method generates cases with description and solution based on the database
     * simframepilotver02 with some modifications. The table Sorting has been added
     * which keeps track of all the sorting operations which have been done on a site
     * on a given date. There are in total 52 such cases, and these again have subcases
     * which are partly represented in fishgrouprelation.
     * 
     * In this prototype we ignore the fact that there are 52 cases and simply 
     * assume that a case is given by one/many sending production units to a
     * single production unit. In a later version it should be able to create 
     * cases which are given by many to many relationships between this units.
     * 
     * Please forgive the quality of the coding, the main goal was a prototype, 
     * not a perfect system ;P
     * @return Collection of CBRCase (CaseBase) 
     */
    public Collection<CBRCase> generateCases(Connection connection) {
        Collection<CBRCase> caseBase = new ArrayList<CBRCase>();

        try{
            this.dropAndCreateDatabase(connection, myCaseBase);
            Statement statement = connection.createStatement();
            /* Sorting JOIN ON AquacultureSite
             * | idSorting:INT | DateTime:DATETIME | AquacultureSite_idAquacultureSite:INT | capacity:INT |
             */
            statement.execute(CaseGenerator.SQL_SORTING);
            ResultSet resultSet = statement.getResultSet();
            
            while(resultSet.next()) {
                // Get the data from sorting
                Integer idSorting = resultSet.getInt("idSorting");
                Integer capacity = resultSet.getInt("capacity");
                Integer siteID = resultSet.getInt("AquacultureSite_idAquacultureSite");
                Date dateTime = resultSet.getDate("DateTime");

                MeasurementType<OxygenInstance> oxygen = 
                        new MeasurementType<OxygenInstance>(new OxygenInstance());
                MeasurementType<TemperatureInstance> temperature = 
                        new MeasurementType<TemperatureInstance>(new TemperatureInstance());
                // Get the measurement for temperature and oxygen
                this.setMeasurements(connection, temperature, oxygen, siteID, dateTime.toString());
                
                // find the distinct receiving fishgroups
                Statement statement2 = connection.createStatement();
                statement2.execute(String.format(CaseGenerator.SQL_DISTINCT_RECEIVING_FG, idSorting));
                ResultSet distinct = statement2.getResultSet();

                // Create a Case for each distinct suboperation(distinct receiving fishgroup)
                while(distinct.next()) {

                     // Get the id of the receiving fishgroup
                    Integer receivingFishGroup = distinct.getInt("idReceivingFishGroup");

                    // create a new Case
                    CBRCase currentCase = new CBRCase();
                    caseBase.add(currentCase);
                    
                    // Add a case description
                    CaseDescription caseDescription = new CaseDescription();
                    currentCase.setDescription(caseDescription);
                    
                    // Connect a aquaculturesite to the case
                    AquacultureSite aquacultureSite = new AquacultureSite();
                    aquacultureSite.setSiteID(siteID);
                    aquacultureSite.setCapacity(capacity);
                                        
                    // Check if the site has already been used
                    // Implemented because of a error with using objects who are equal(.equals-method) in org.hibernate.Session
                    // Session doesnt distinguish between equal object saved in session.
                    int index = 0;
                    if((index = this.sites.indexOf(aquacultureSite))==-1)
                        this.sites.add(aquacultureSite);
                    else
                        aquacultureSite = this.sites.get(index);

                    // set up the relationship between site and case
                    aquacultureSite.addDesc(caseDescription);
                    caseDescription.setAquacultureSite(aquacultureSite);
                    
                    // add the date
                    caseDescription.setDateTime(dateTime);
                    
                    // relationship between temperature and case
                    caseDescription.setTemperature(temperature);
                    temperature.addDescription(caseDescription);
                    
                    // relationship between oxygen and case
                    caseDescription.setOxygen(oxygen);
                    oxygen.addDescription(caseDescription);
                    
                                       
                    // query for the aquacultureprodunit where the receiving fishgroup is located.
                    // Assume that we only have a many-to-one relationship between sending and receiving where
                    // we have many sending units and only one receiving.
                    Statement s = connection.createStatement();
                    s.execute(String.format(CaseGenerator.SQL_AQUACULTUREPRODUNIT_FROM_FISHGROUP, receivingFishGroup));
                    ResultSet localResult = s.getResultSet();
                    Integer aqprodunit = 0;
                    while(localResult.next())
                        aqprodunit = localResult.getInt("AquacultureProdUnit_idAquacultureProdUnit");
                    s.close();
                    
                    // Create a object of aquacultureprodunit and add to case
                    AquacultureProdUnit aquacultureProdUnit = new AquacultureProdUnit();
                    aquacultureProdUnit.setAquacultureProdUnitID(aqprodunit);
                    caseDescription.setReceivingProdUnit(aquacultureProdUnit);
                    
                   //find vaccine, hacthery and speciesorigin of the fishgroup in the receiving prod unit
                    this.findFishInfo(connection, receivingFishGroup, aquacultureProdUnit);

                    // now find the sending production units
                    s = connection.createStatement();
                    s.execute(String.format(CaseGenerator.SQL_SENDING_AQUACULTUREPRODUNIT_FROM_RECEIVING_FG, receivingFishGroup));
                    localResult = s.getResultSet();
                    
                    // Iterate the resultset, add the produnits to a list. Add this list as sending in the CaseDescription object
                    List<AquacultureProdUnit> sending = new ArrayList<AquacultureProdUnit>();
                    while(localResult.next()) {
                        AquacultureProdUnit sendingProdUnit = new AquacultureProdUnit(localResult.getInt("sending"));
                        sendingProdUnit.setDescriptionID(caseDescription);
                        this.findIndividCount(connection, sendingProdUnit, dateTime.toString());
                        this.findStarvationDays(connection, sendingProdUnit, dateTime.toString());
                        sending.add(sendingProdUnit);
                    }
                    caseDescription.setSendingProdUnits(sending);
                    double individcount = 0.0;
                    for(AquacultureProdUnit apu : caseDescription.getSendingProdUnits()) {
                        Double individCount = apu.getIndividCount();
                        if(individCount!=null)
                            individcount += individCount;
                    }
                    
                    // Add a case solution
                    Integer days = 14;
                    CaseSolution caseSolution = this.generateSolutions(receivingFishGroup, dateTime, days, connection);
                    currentCase.setSolution(caseSolution);
                    caseSolution.setCaseDescription(caseDescription);
                    caseDescription.setCaseSolution(caseSolution);
                    // Risk management
                    this.getRisk(caseSolution);
                    s.close();

                }
            }
            statement.close();
            connection.close();
//            for(int i = 0; i < death.size(); i++)
//                System.out.println("["+i+"] = "+death.get(i));
//            System.out.println(death.toString());
//            Collections.sort(death);
//            System.out.println(death.toString());
//            Double min = death.get(0);
//            Double max = death.get(death.size()-1);
//            Double range = max - min;
//            Double part = range/3;
//            
//            for(CBRCase cBRCase : caseBase)
//            {
//                CaseSolution cs = (CaseSolution) cBRCase.getSolution();
//                Double current = cs.getDeathRatio();
//                if(current == null)
//                    cs.setRisk("No death");
//                else if(current< min+part-0.04)
//                    cs.setRisk("Low");
//                else if(current >= min+part-0.04 && current < min+(part*2))
//                    cs.setRisk("Medium");
//                else
//                    cs.setRisk("High");
//                System.out.println(cs.getRisk());
//            }
            // Update the trends in temperature and oxygen with help of LinearApproximation
            temperatureLinearApproximation.createMeasurementAbstractions();
            oxygenLinearApproximation.createMeasurementAbstractions();

            System.out.println("Temp: "+temperatureLinearApproximation.maximum+", "+temperatureLinearApproximation.minimum);
            System.out.println("Temp: "+oxygenLinearApproximation.maximum+", "+oxygenLinearApproximation.minimum);
            
            // Do this for each case
            for(CBRCase cbrc : caseBase) {
                CaseDescription caseDescription = (CaseDescription) cbrc.getDescription();
                // Temperature
                ApproximationNumber anLocal = caseDescription.getTemperature().getApproximation();
                String trend = temperatureLinearApproximation.getTrend(anLocal);
                caseDescription.getTemperature().setTrend(trend);
                
                //oxygen
                anLocal = caseDescription.getOxygen().getApproximation();
                trend = oxygenLinearApproximation.getTrend(anLocal);
                caseDescription.getOxygen().setTrend(trend);
            }
        }catch(Exception e) {
            org.apache.commons.logging.LogFactory.getLog(this.getClass()).error(e);
        }
        return caseBase;
    }
    
    public void retainCases(Collection<CBRCase> caseBase) {
        try{
            // Load configuration and session
            Configuration cfg = new Configuration();
            cfg.configure("my/cbr/database/resources/Hibernate_config_CaseGenerator.cfg.xml");
            SessionFactory sf = cfg.buildSessionFactory();
            int caseCount = 0;
            Session session = sf.openSession();
            
            // Save each case to database
            for(CBRCase currentCase : caseBase)
            {
                CaseDescription caseDescription = (CaseDescription) currentCase.getDescription();
                org.hibernate.Transaction transaction = session.beginTransaction();

                // Save parents first then the child(caseDescription)
                session.saveOrUpdate(caseDescription.getTemperature());
                session.saveOrUpdate(caseDescription.getOxygen());
                session.saveOrUpdate(caseDescription.getAquacultureSite());
                session.saveOrUpdate(caseDescription);
                transaction.commit();
                org.apache.commons.logging.LogFactory.getLog(CaseGenerator.class).info(
                        String.format("Done inserting case %d into %s",caseCount++,CaseGenerator.myCaseBase));
            }
            session.close();
                org.apache.commons.logging.LogFactory.getLog(CaseGenerator.class).info(
                        String.format("Inserted all cases into %s: Case count = %d",CaseGenerator.myCaseBase,caseCount));
        }catch(Exception e) {
            org.apache.commons.logging.LogFactory.getLog(CaseGenerator.class).error(e);
        }
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // Create the case generator
        CaseGenerator c = new CaseGenerator();
    }
    
    // Some strings which are used in the case generation
    public static final String RESULTTYPENAME_TEMPERATURE = "SeaTemperatureMedium";
    public static final String RESULTTYPENAME_OXYGEN = "OxygenMedium";
    public static final String RESULTTYPENAME_COUNT = "IndividCount";

    /**
     * Various SQLs for use in the case generation
     */
    public static final String SQL_SORTING = "SELECT * FROM SORTING JOIN AQUACULTURESITE ON AquacultureSite_idAquacultureSite "
            + "WHERE AquacultureSite_idAquacultureSite=AQUACULTURESITE.idAquacultureSite";

    public static final String SQL_MEASUREMENTNUMERICAL = "SELECT Value, ResultTypeName, DateTime FROM "
                        + "AQUACULTURESITE, RESULTTYPE, MEASUREMENTNUMERICAL, OBSERVATION, REFERENCEPOINT "
                        + "where idAquacultureSite=%d AND "
                        + "idAquacultureSite=referencepoint.AquacultureSite_idAquacultureSite AND "
                        + "Observation_idObservation=idObservation AND "
                        + "ReferencePoint_idReferencePoint=idReferencePoint AND "
                        + "idResultType=ResultType_idResultType AND "
                        + "(ResultTypeName='%s' OR ResultTypeName='%s') AND "
                        + "DateTime BETWEEN DATE_ADD('%s',INTERVAL -14 DAY) AND "
                        + "'%s' ORDER BY DateTime ASC";

    public static final String SQL_INDIVIDCOUNT = "SELECT Value, ResultTypeName, DateTime FROM "
                        + "RESULTTYPE, MEASUREMENTNUMERICAL, OBSERVATION, REFERENCEPOINT "
                        + "where AquacultureProdUnit_idAquacultureProdUnit=%d AND "
                        + "ReferencePoint_idReferencePoint=idReferencePoint AND "
                        + "idResultType=ResultType_idResultType AND "
                        + "ResultTypeName='%s' AND "
                        + "Observation_idObservation=idObservation AND "
                        + "DateTime=DATE_ADD('%s',INTERVAL -1 DAY)";

    public static final String SQL_DISTINCT_RECEIVING_FG = "SELECT DISTINCT idReceivingFishGroup from fishgrouprelation "
                        + "where Sorting_idSorting=%s";

    public static final String SQL_SENDING_FG2 = "SELECT idFishGroup, AquacultureProdUnit_idAquacultureProdUnit, StartTime, EndTime "
            + "FROM simframepilotver02.fishgroup WHERE ('%s' between StartTime AND DATE_ADD(EndTime, INTERVAL 1 DAY)) AND AquacultureProdUnit_idAquacultureProdUnit=%d";

    public static final String SQL_SENDING_FG = "SELECT idFishGroup, AquacultureProdUnit_idAquacultureProdUnit, StartTime "
            + "FROM fishgroup WHERE EndTime is NULL AND AquacultureProdUnit_idAquacultureProdUnit=%d";

    public static final String SQL_VACCINE = "SELECT FishGroup_idFishGroup, idVaccine, Description "
            + "FROM fishgroup_has_vaccine, vaccine "
            + "WHERE idVaccine=Vaccine_idVaccine and FishGroup_idFishGroup=%d";
    
    public static final String SQL_FEEDING = "SELECT idFeeding, AquacultureProdUnit_idAquacultureProdUnit, Amount, StartTime "
            + "FROM feeding "
            + "WHERE StartTime BETWEEN DATE_ADD('%s', interval %d day) AND '%s' "
            + "AND AquacultureProdUnit_idAquacultureProdUnit=%d "
            + "ORDER BY StartTime DESC";
    
    public static final String SQL_STARTFISHGROUP_INFORMATION = "SELECT FishGroup_idFishGroup, StartFishGroup_idStartGroup, HatcheryCompanyID, SpeciesOrigin "
            + "FROM fishgroup_has_startfishgroup, startfishgroup "
            + "WHERE Fishgroup_idFishGroup=%d and "
            + "idStartGroup=StartFishGroup_idStartGroup";

    public static final String SQL_AQUACULTUREPRODUNIT_FROM_FISHGROUP = 
            "SELECT AquacultureProdUnit_idAquacultureProdUnit FROM FISHGROUP WHERE idFishGroup=%d";
    
    public static final String SQL_SENDING_AQUACULTUREPRODUNIT_FROM_RECEIVING_FG = 
            "SELECT AquacultureProdUnit_idAquacultureProdUnit AS sending FROM FISHGROUP, FISHGROUPRELATION "
            + "WHERE idSendingFishGroup=idFishGroup and idReceivingFishGroup=%d";
    
    public static final String SQL_CAUSEOFDEATH_FOR_FISHGROUP_GIVEN_DATE = 
            "SELECT Cause, Count, idFishGroup, DateTime FROM CAUSEOFDEATH, DEADCOUNT, FISHGROUP "
            + "WHERE DeadCount_idDeadCount=idDeadCount AND "
            + "idFishGroup=FishGroup_idFishGroup AND "
            + "idFishGroup=%d AND "
            + "DateTime BETWEEN '%s' and DATE_ADD('%s', INTERVAL %d DAY)";

    public static final String SQL_GET_SITE_INFO = "SELECT idAquacultureSite, Capacity "
            + "FROM simframepilotver02.aquaculturesite WHERE idAquacultureSite=%d";
}
